# -*- coding: utf-8 -*-
import json
import multiprocessing
import traceback
from datetime import datetime

import numpy as np
import pandas as pd
import statsmodels.api as sm
from pandas.tseries.offsets import DateOffset
import applications.pycube.models.calc_optimize as calc_optimize
import applications.pycube.models.alpha_constant as alpha_constant
import applications.pycube.models.alpha_common as alpha_common

# @cache(request.env.path_info+"/get_alpha_stock_data", time_expire=auth.settings.expiration, cache_model=cache.ram)
# def get_alpha_stock_data():
#     return db.executesql("select * from alpha_stock_data")

# @cache(request.env.path_info+"/get_alpha_hs300_weight", time_expire=auth.settings.expiration, cache_model=cache.ram)
def get_alpha_hs300_weight(tradedate):
    return db.executesql("select tradecode,weight from alpha_hs300_weight where date = '" + tradedate + "'")

@cache(request.env.path_info+"/get_hs300_data", time_expire=auth.settings.expiration, cache_model=cache.ram)
def get_hs300_data():
    return db.executesql("select * from alpha_hs300_data")

@cache(request.env.path_info+"/get_stock_industry", time_expire=auth.settings.expiration, cache_model=cache.ram)
def get_stock_industry():
    return db.executesql("select * from alpha_stock_industry")

# @cache(request.env.path_info+"/get_st_tradecode", time_expire=auth.settings.expiration, cache_model=cache.ram)
def get_st_tradecode():
    return db.executesql("SELECT tradedate,tradecode FROM alpha_st_tradecode")

# @cache(request.env.path_info+"/get_trade_create_data", time_expire=auth.settings.expiration, cache_model=cache.ram)
def get_trade_create_data():
    return db.executesql("SELECT tradecode,ipo_date FROM alpha_trade_create_data")

def get_bench_industry_weight(tradedate):
    """
    计算 沪深300 行业权重
    """
    bench_industry_weight = db.executesql("select t1.industry,sum(t2.weight) as bench_industry_weight from alpha_stock_industry t1 inner join alpha_hs300_weight t2 on t1.tradecode = t2.tradecode where t2.date = '" + tradedate + "' group by t1.industry")
    bench_industry_weight_df = pd.DataFrame(data=bench_industry_weight, columns=['industry', 'bench_industry_weight'])
    return bench_industry_weight_df

def weight_var(size, var=90.0):
    # 方差权重向量
    return np.power(1 / 2.0 ** (1 / var), list(range(size - 1, -1, -1)))

def weight_cov(size, cov=480.0):
    # 协方差权重向量
    return np.power(1 / 2.0 ** (1 / cov), list(range(size - 1, -1, -1)))

def F_d(f,var,cov):
    f_var = np.cov(f, rowvar=False, aweights=weight_var(len(f),var))  # 因子收益率的方差
    # print f_var
    f_cov = np.cov(f, rowvar=False, aweights=weight_cov(len(f),cov))  # 因子收益率的协方差
    # print f_cov
    return f_cov - np.diagflat(np.diag(f_cov)) + np.diagflat(np.diag(f_var))

def pre_oneday_volatility(df,var=90):#
    varWeight = weight_var(df.shape[0],var)  # T天的var权重
    varWeight = varWeight/varWeight.sum()
    return (varWeight*df**2).sum()**0.5

def get_btf(f, lag=63,delay=1):
    f = pd.DataFrame(f)
    df = f.shift(delay)#k t 为第 t-1 天预测的第 t 天的因子收益的波动率
    f_std = df.rolling(window=lag).apply(pre_oneday_volatility)
    f_btf = (((df/f_std)**2).mean(axis=1))**0.5
    f_btf.dropna(inplace=True)
    return f_btf

def get_btf_test(f, d=21):
    varWeight = weight_var(f.shape[0])  # T天的var权重
    btf = []  # 需对应时间序列顺序
    for x in range(f.shape[0] - d - 1):
        # 𝜎_𝑘𝑡
        Q_kt = []  # k个因子 第t-1天预测的第t天的因子收益的波动率
        for y in range(f[x:x + d].shape[1]):  # 循环因子个数
            average = np.average(f[:, y], weights=varWeight)
            variance = np.average((f[:, y] - average) ** 2, weights=varWeight)
            # average = np.average(f[x:x + d][:, y], weights=varWeight[x:x + d])
            # variance = np.average((f[x:x + d][:, y] - average) ** 2, weights=varWeight[x:x + d])
            Q_kt.append(np.power(variance, 0.5))  # 𝜎_𝑘𝑡
        # 截面偏差统计量B_t^F = #√(1/K ∑_k▒(f_kt/σ_kt )^2 )
        btf_i = (sum(np.power(f[x + d + 1, :] / np.array(Q_kt, dtype=float), 2)) / len(Q_kt)) ** 0.5
        btf.append(btf_i)
    return btf

def calc_newey_west(data, var_lag=2,cov_lag=5,mtd = 21):
    """
    Newey-West 调整  Newey-West方法调整日频率协方差序列存在的序列相关性，进而得到月频率的方差-协方差矩阵
    :param data:
    :param var_lag:
    :param cov_lag:
    :param mtd:
    :return:
    """
    C_var_res = pd.DataFrame()
    C_cov_res = pd.DataFrame()
    for i in range(1, var_lag + 1):
        data_shift = data.shift(i)[i:].T
        data_cov = data[:-i].T
        weights = weight_btf(data_cov.shape[1])
        C_var = np.diagflat(np.diag(np.cov(data_shift,data_cov,aweights=weights)))
        C_var_res += (C_var + C_var.T)*(1-i/(1+var_lag))

    for i in range(1, cov_lag + 1):
        data_shift = data.shift(i)[i:].T
        data_cov = data[:-i].T
        weights = weight_btf(data_cov.shape[1])
        C_cov = np.cov(data_shift,data_cov,aweights=weights)
        C_cov = C_cov - np.diag(C_cov)
        C_cov_res += (C_cov + C_cov.T)*(1-i/(1+var_lag))
    C_res = C_var_res + C_cov_res
    C_nw = mtd * (data + C_res)
    return C_nw

def weight_btf(size, cov=42.0):
    return np.power(1 / 2.0 ** (1 / cov), list(range(size - 1, -1, -1)))

def update_alpha_policy(flag,**arg_dict):
    # UPDATE alpha_policy SET start_date = '1', end_date = '2' ,select_factor_json ='3' WHERE policy_name = 'test3'
    if 'step2' == flag:
        sql = "UPDATE alpha_policy SET start_date = '"+ arg_dict['begindate'] +"', end_date = '"+arg_dict['enddate']+"' ,select_factor_json ='"+arg_dict['step2_json']+"',style_factors = '"+ arg_dict['style_factors'] +"', industry_factors = '"+arg_dict['industry_factors']+"', all_factors = '"+arg_dict['all_factors']+"',comb_end_date = 0 WHERE policy_name = '"+arg_dict['policy_name']+"'"
    if 'step3' == flag:
        sql = "UPDATE alpha_policy SET style_factors = '"+ arg_dict['style_factors'] +"', industry_factors = '"+arg_dict['industry_factors']+"', all_factors = '"+arg_dict['all_factors']+"' WHERE policy_name = '"+arg_dict['policy_name']+"'"
    if 'step4' == flag:
        sql = "UPDATE alpha_policy SET predict_json = '" + arg_dict['predict_json'] + "' WHERE policy_name = '" + arg_dict['policy_name'] + "'"
    if 'step6' == flag:
        sql = "UPDATE alpha_policy SET comb_start_date = '"+ arg_dict['begindate'] +"', comb_end_date = '"+arg_dict['enddate']+"' ,comb_weights ='"+arg_dict['step6_json']+"' ,alpha_factors ='"+arg_dict['alpha_factors']+"' ,bata_factors ='"+arg_dict['bata_factors']+"' WHERE policy_name = '"+arg_dict['policy_name']+"'"
    db.executesql(sql)

def test_valid():
    """
    因子选择 - 组合风险估计
    :return:
    """
    try:
        step2_dict = {}
        step2_dict['stockpool'] = request.post_vars['step2_stockpool']
        step2_dict['step2chk'] = request.post_vars['step2chk[]']
        start_date = request.post_vars['begindate']
        end_date = request.post_vars['enddate']
        policy_id = request.post_vars['policy_id'] #策略名称
        policy_name = request.post_vars['policy_name'] #策略名称

        factors_list = alpha_constant.stock_data_column_list
        factors_name = request.post_vars['factors[]']
        factors_tpye = request.post_vars['factorstpye[]']
        x_list=[]
        if factors_name==None:
            return response.json({'status': 'error', 'msg': '请选择因子！'})

        if type(factors_name) == type(''):
            x_list.append(factors_name)
        elif type(factors_name) == type([]):
            for factors_name_a in factors_name:
               if factors_name_a in factors_list :
                   x_list.append(factors_name_a)

        if len(x_list)==0:
            return response.json({'status': 'error', 'msg': '选择的因子数据不存在！'})

        if len(x_list)==1:
            return response.json({'status': 'error', 'msg': '选择的一个以上因子数据！'})

        df_obj = get_base_data(start_date,end_date) # 策略基本数据
        code_list = df_obj['tradecode'].drop_duplicates()
        date_list = df_obj['tradedate'].drop_duplicates().sort_values()

        X =df_obj[x_list]

        stockrets=get_hs300_data()
        df_hs300_x = pd.DataFrame(data=stockrets, columns=alpha_constant.hs300_data_column_list)
        df_hs300_x = df_hs300_x[df_hs300_x['tradedate'].isin(date_list)]    # 沪深300 收益

        industry = get_industry_dummies(code_list)        # 行业因子哑变量 DataFrame
        industryname = industry.columns.tolist()

        effective_factors = []
        effective_factors.extend(x_list)
        effective_factors.extend(industryname)
        # 保存因子选择阶段参数
        update_alpha_policy('step2', begindate=start_date, enddate=end_date, policy_name=policy_name,
                            step2_json=json.dumps(step2_dict), style_factors=json.dumps(x_list),
                            industry_factors=json.dumps(industryname), all_factors=json.dumps(effective_factors))

        '''第三步 组合风险估计'''
        model_dict = alpha_common.build_model(db,policy_id, start_date, end_date,df_obj,'test_valid')
        f = model_dict['f']
        t = model_dict['t']

        data_t = np.mean(abs(np.array(t)), axis=0).reshape([1, -1])
        data_t_nan = np.isnan(data_t)
        data_t[data_t_nan] = 0

        data_t_2 = (np.sum(abs(np.array(t)) > 2, axis=0) / float(np.array(t).shape[0])).reshape([1, -1])
        data_1_f = np.cumprod((np.insert(np.array(f), 0, values=np.zeros(np.array(f).shape[1]), axis=0) + 1), axis=0)
        data_Annual_Factor_Return = (data_1_f[-1:] ** (252.0 / np.array(f).shape[0]) - 1).reshape([1, -1])
        data_Annual_Factor_Volatility = np.std(np.array(f), axis=0) * (252 ** 0.5) * 100.0

        data_r_f = np.cumprod((np.insert(df_obj.ix[date_list.index].risk_free.values, 0, values=np.zeros(1), axis=0) + 1))
        data_R_f = (data_r_f[-1:] ** (252.0 / np.array(f).shape[0]) - 1) * 100.0
        data_Factor_Return_Sharpraio = (data_Annual_Factor_Return - data_R_f) / data_Annual_Factor_Volatility
        data_Correl_With_HS300 = []
        data_Variance_Inflation_Factor = []
        for i in range(len(x_list)):
            data_Correl_With_HS300.append(np.correlate(np.array(f)[:, i], df_hs300_x['rf_rets'])[0])
            Mod_wls = sm.WLS(X[x_list[i]], X[x_list[0:i] + x_list[i + 1:]])
            res_wls = Mod_wls.fit()
            data_Variance_Inflation_Factor.append(1.0 / (1.0 - res_wls.rsquared))

        table = '<table id="tb_departments"></table>'
        tableIndustry = '<table id="tb_TableInitIndustry"></table>'

        tablelist=[]
        for i in range(len(x_list)):
            dict={}
            dict['id'] = i
            dict['name'] = x_list[i]
            dict['t']=str(round(data_t.tolist()[0][i],2))
            dict['t2'] = str(round(data_t_2.tolist()[0][i],2))
            dict['r'] = str(round(data_Annual_Factor_Return.tolist()[0][i],2))
            dict['v'] = str(round(data_Annual_Factor_Volatility.tolist()[i],2))
            dict['s'] = str(round(data_Factor_Return_Sharpraio.tolist()[0][i],2))
            dict['c'] = str(round(data_Correl_With_HS300[i],2))
            dict['vif']=str(round(data_Variance_Inflation_Factor[i],2))
            if data_t_2.tolist()[0][i]>0.05:
                dict['l'] ="1"
            else:
                dict['l'] ="0"
            tablelist.append(dict)
        session.tablelist=tablelist

        tableindustrylist=[]
        xx=len(x_list)
        for i in range(xx,(len(industryname)+xx)):
            dict={}
            dict['id'] = i
            dict['name'] = industryname[i-xx]
            dict['t']=str(round(data_t.tolist()[0][i],2))
            dict['t2'] = str(round(data_t_2.tolist()[0][i],2))
            dict['r'] = str(round(data_Annual_Factor_Return.tolist()[0][i], 2))
            dict['v'] = str(round(data_Annual_Factor_Volatility.tolist()[i], 2))
            dict['s'] = str(round(data_Factor_Return_Sharpraio.tolist()[0][i], 2))
            if data_t_2.tolist()[0][i]>0.05:
                dict['l'] ="1"
            else:
                dict['l'] ="0"
            tableindustrylist.append(dict)
        session.tableindustrylist=tableindustrylist

        return response.json({'status': 'ok','msg':'msg','table':table,'tableIndustry':tableIndustry})
    except Exception, e:
        print traceback.format_exc()
        return response.json({'status': 'error', 'msg': str(e)})


def order_table(sortName,sortOrder,table_list):
    new_table_list = []
    new_table_df = pd.DataFrame(table_list)
    new_table_df[sortName] = new_table_df[sortName].astype('float64')
    if sortOrder.__eq__("desc"):
        new_table_df.sort_values(sortName,inplace=True, ascending=False)
    else:
        new_table_df.sort_values(sortName,inplace=True, ascending=True)
    for index, row in new_table_df.iterrows():
        new_table_list.append(row.to_dict())
    return new_table_list

def gettabledata():
    try:
        sortOrder = request.get_vars['sortOrder']
        sortName = request.get_vars['sortName']
        lstRes = order_table(sortName,sortOrder,session.tablelist)
        return response.json({'status': 'ok','total':len(lstRes),'rows':lstRes})
    except Exception, e:
        print traceback.format_exc()
        return response.json({'status': 'error', 'msg': str(e)})

def gettableIndustrydata():
    sortOrder = request.get_vars['sortOrder']
    sortName = request.get_vars['sortName']
    lstRes = order_table(sortName, sortOrder, session.tableindustrylist)
    return response.json({'status': 'ok','total':len(lstRes),'rows':lstRes})

def get_factor_list():
    try:
        sql = "select json from alpha_policy_json where type = " + alpha_constant.pure_factor_grids_type + " and policy_id=" + request.get_vars['policy_id']
        result = db.executesql(sql)
        sortOrder = request.get_vars['sortOrder']
        sortName = request.get_vars['sortName']
        lstRes = order_table(sortName, sortOrder, json.loads(result[0][0]))
        return response.json({'status': 'ok','total':len(lstRes),'rows':lstRes})
    except Exception, e:
        print traceback.format_exc()
        return response.json({'status': 'error', 'msg': str(e)})

def get_valid_factor():
    """
    因子有效性检验
    :return:
    """
    fdata = request.post_vars['fdata[]']
    industry_fdata = request.post_vars['industry_fdata[]']

    tablelist = session.tablelist
    tableindustrylist = session.tableindustrylist

    valid_factor_dict = {}

    effective_style_factors = [] # 有效的风格因子code_list
    effective_industry_factors = [] # 有效的行业因子code_list
    effective_factors = [] # 所有的有效因子code_list

    for i,namef in enumerate(fdata):
        if namef=="1":
            valid_factor_dict[tablelist[i]['name']] = tablelist[i]['l']
            effective_style_factors.append(tablelist[i]['name'])
            effective_factors.append(tablelist[i]['name'])

    for i, namef in enumerate(industry_fdata):
        if namef == "1":
            valid_factor_dict[tableindustrylist[i]['name']] = tableindustrylist[i]['l']
            effective_industry_factors.append(tableindustrylist[i]['name'])
            effective_factors.append(tableindustrylist[i]['name'])

    policy_name = request.post_vars['policy_name']
    # session.valid_factor_dict = valid_factor_dict
    session.effective_factors = effective_factors   # 所有的有效因子code_list
    session.effective_style_factors = effective_style_factors # 有效的风格因子code_list
    session.effective_industry_factors = effective_industry_factors # 有效的行业因子code_list

    update_alpha_policy('step3',style_factors=json.dumps(effective_style_factors),industry_factors=json.dumps(effective_industry_factors),all_factors=json.dumps(effective_factors),policy_name=policy_name)
    print valid_factor_dict

def construct_model():
    """
    收益与风险 - 模型构建
    :return: 模型结果
    """
    try:
        '''第4章 组合风险的预测'''
        earningsforecast = float(request.post_vars['earningsforecast'])
        var_half_life = float(request.post_vars['var_half_life'])
        cov_half_life = float(request.post_vars['cov_half_life'])
        err_half_life = float(request.post_vars['err_half_life'])
        policy_name = request.post_vars['policy_name']
        policy_id = request.post_vars['policy_id']
        start_date = request.post_vars['begindate']
        end_date = request.post_vars['enddate']

        predict_dict={}
        predict_dict['var_half_life'] = var_half_life
        predict_dict['cov_half_life'] = cov_half_life
        predict_dict['err_half_life'] = err_half_life
        predict_dict['earningsforecast'] = earningsforecast
        update_alpha_policy('step4', predict_json=json.dumps(predict_dict), policy_name=policy_name)

        policy_dict = get_policy(policy_id=policy_id)
        new_x_list = policy_dict['all_factors']
        style_factors = policy_dict['style_factors']
        ite_count = len(style_factors)

        if alpha_constant.restart_flag == 0:# 0:重新计算;1:从db中读取
            df_date_range = get_date_range(start_date, end_date)  # 滚动日期集合
            if df_date_range.size == 0:
                return response.json({'status': 'error'})

            sql = "DELETE FROM alpha_construct_model where policy_name = '" + policy_name + "'"  # 删除已有的策略轮动权重履历
            db.executesql(sql)

            '''第五步 风险模型收益预测 '''
            if alpha_constant.multi_single_flag == 0: # 0:并行计算;1:单进程计算
                process_pool = multiprocessing.Pool(alpha_constant.POOL_SIZE)
                parm_list = []
                for i, date_range in enumerate(df_date_range.values):
                    parm_list.append((db,policy_id,policy_name,date_range,var_half_life,cov_half_life,err_half_life,ite_count,i))
                results = process_pool.map(calc_optimize.multi_pure_factor_portfolios, parm_list)
                process_pool.close()
                process_pool.join()

                for result in results:
                    print result
                    db.executesql(result)

            else: # 单进程执行
                results = []
                for i, date_range in enumerate(df_date_range.values):
                    results.append(calc_optimize.pure_factor_portfolios(db, policy_id, policy_name, date_range, var_half_life, cov_half_life, err_half_life, ite_count, i))

                for result in results:
                    db.executesql(result)

        session.retest_dict = {'policy_returns_acc': [], 'benchmark_returns_acc': [], 'annualized_year': [], 'algor_volat': [], 'max_drawdown': [], 'ir': [], 'benchmark_year': []}
        if earningsforecast == 1:
            method_name = 'Lagrange乘子法'
            # ir_df = pd.DataFrame(data=df_obj[['tradedate', 'tradecode', 'Y_rets']], columns=['tradedate', 'tradecode', 'Y_rets'])
            #
            # newPd = ir_df.pivot_table(index='tradedate', columns='tradecode', values='Y_rets')
            #
            # df_hs300_x = pd.DataFrame(data=get_hs300_data(),columns=alpha_constant.hs300_data_column_list)
            # df_hs300_x_new = df_hs300_x[df_hs300_x['tradedate'].isin(newPd.index)]['rf_rets'].values
            #
            # gg_s = []
            # print '各只股票的超额收益 和沪深 300 指数的超额收益'
            # # df_hs300_x=np.random.randn(day).reshape(day,1)
            # falpha = []
            # df_hs300_x_new = sm.add_constant(df_hs300_x_new).astype('float64')
            # for num_w in range(len(w_parameter)):
            #     y_array = np.dot(newPd, np.array(w_parameter).T)[:, num_w]
            #     if f_parameter[num_w] == 0:
            #         print num_w
            #         falpha.append(0)
            #         continue
            #     model1 = sm.WLS(y_array, df_hs300_x_new)
            #     res_wls1 = model1.fit()
            #     a_p = res_wls1.params[0]
            #     w_p = np.std(res_wls1.resid)
            #     ir = (a_p * 21) / (w_p * (21 ** .5))
            #
            #     print 'ir=', ir
            #     print 'f̂ alpha', f_parameter[num_w] ** 0.5 * ir
            #     gg = float(np.dot(df_last_day_data.T[num_w, :], (np.array(w_parameter[num_w]) - select_hs300_weight).T))
            #     print 'gg', gg
            #     gg_s.append(gg)
            #     falpha.append(f_parameter[num_w] ** 0.5 * ir / gg)
            #
            #     comb_weights_df = pd.DataFrame(data=np.array([df_last_day_data.index,new_x_list[num_w]]).T,columns=['tradecode', 'weight_w'])
            #     calc_retest(0, None, None,start_date=start_date,end_date=end_date,comb_weights=comb_weights_df,df_obj=df_obj)  # 计算 指标
            # session.f_pre=falpha
        else:
            method_name = 'EWMA'
            sql = "SELECT retest_start_date,retest_end_date,model_weights,w_tradecode FROM alpha_construct_model WHERE policy_name = '" + policy_name + "' order by  seq"
            retest_obj = db.executesql(sql)
            if len(retest_obj)==0:
                return response.json({'status': 'error', 'msg': '新策略必须重新执行'})
            retest_df = pd.DataFrame(data=retest_obj,columns=['retest_start_date', 'retest_end_date', 'model_weights', 'w_tradecode'])

            total_model_dict = alpha_common.build_model(db,policy_id, start_date, end_date, step='test_valid')  # 模型构建
            f = total_model_dict['f']
            f_pre = np.average(np.array(f), axis=0, weights=weight_var(len(f), var=252)) * 21
            sql = "DELETE FROM alpha_policy_json where type = " + alpha_constant.f_pre_type + " and policy_id = '" + policy_id + "'"  # 删除已有的策略轮动权重履历
            db.executesql(sql)
            sql = "INSERT INTO alpha_policy_json(type,policy_id,json)  VALUES(" + alpha_constant.f_pre_type + ",'" + policy_id + "','" + json.dumps(f_pre.tolist()) + "')"
            db.executesql(sql)

            all_date_list = []
            for index in range(ite_count):
                retest_dict = calc_retest02(retest_df,index)
                session.retest_dict['annualized_year'].append(retest_dict['annualized_year'])
                session.retest_dict['algor_volat'].append(retest_dict['algor_volat'])
                session.retest_dict['ir'].append(retest_dict['ir'])
                session.retest_dict['max_drawdown'].append(retest_dict['max_drawdown'])
                session.retest_dict['policy_returns_acc'].append(retest_dict['policy_returns_acc'].tolist())
                session.retest_dict['benchmark_returns_acc'].append(retest_dict['benchmark_returns_acc'].tolist())
                all_date_list.append(retest_dict['all_date_list'].tolist())

        factors_grids = []
        for i, factor in enumerate(style_factors):
            dict = {}
            dict['id'] = i
            dict['name'] = factor
            dict['annualized_year'] = str(round(session.retest_dict['annualized_year'][i], 2))
            dict['algor_volat'] = str(round(session.retest_dict['algor_volat'][i], 2))
            dict['ir'] = str(round(session.retest_dict['ir'][i], 2))
            dict['max_drawdown'] = str(round(session.retest_dict['max_drawdown'][i], 2))
            dict['l'] = "2" # 1:alpha, 2:bata
            factors_grids.append(dict)
        sql = "DELETE FROM alpha_policy_json where type = " + alpha_constant.pure_factor_grids_type + " and policy_id = '" + policy_id + "'"  # 删除已有的策略轮动权重履历
        db.executesql(sql)
        sql = "INSERT INTO alpha_policy_json(type,policy_id,json)  VALUES(" + alpha_constant.pure_factor_grids_type + ",'" + policy_id + "','" + json.dumps(factors_grids) + "')"
        db.executesql(sql)

        '''
        模型结构展示
        '''
        table = '<div id="tchar" style="width: 99%; height: 98%; margin: 0 auto"></div>'
        tableFactorsGrids = '<table id="tb_factors_grids"></table>'

        ################### 纯因子组合收益 ######################
        f_pre_df = pd.DataFrame(data=np.array([new_x_list,f_pre]).T,columns=['f_name','f_pre'])
        f_pre_df['f_pre'] = f_pre_df['f_pre'].astype('float64')
        f_pre_df.sort_values('f_pre',inplace=True,ascending=False)
        f_pre_series = []
        f_pre_series_dict = {}
        f_pre_series_dict['name'] = method_name
        f_pre_series_dict['data'] = f_pre_df['f_pre'].values.tolist()
        f_pre_series.append(f_pre_series_dict)

        f_pre_chart = {}
        f_pre_chart['chart'] = {'type': 'column'}
        f_pre_chart['title'] = {'text': '因子预期收益'}
        f_pre_chart['subtitle'] = {'text': ''}
        f_pre_chart['xAxis'] = {'categories': f_pre_df['f_name'].values.tolist()}
        f_pre_chart['yAxis'] = {'title': {'text': '因子预期收益率 (%)'}, 'plotLines': [{'value': 0, 'width': 1, 'color': '#808080'}]}
        f_pre_chart['tooltip'] = {'valueSuffix': '%'}
        f_pre_chart['legend'] = {'layout': 'vertical', 'align': 'right', 'verticalAlign': 'middle', 'borderWidth': 0}
        f_pre_chart['series'] = f_pre_series

        xy_xchar = []
        # for i in enumerate(new_x_list)):
        for i, factor in enumerate(style_factors):
            xy_xchar_dict = {}
            xy_xchar_dict['name'] = factor
            xy_xchar_dict['data'] = session.retest_dict['policy_returns_acc'][i]
            xy_xchar_dict['visible'] = False
            xy_xchar.append(xy_xchar_dict)

        xy_xchar_dict = {}
        xy_xchar_dict['name'] = '基准'
        xy_xchar_dict['data'] = session.retest_dict['benchmark_returns_acc'][0]
        xy_xchar.append(xy_xchar_dict)

        xchar = {}
        xchar['title'] = {'text': '因子累计收益率'}
        xchar['subtitle'] = {'text': ''}
        xchar['xAxis'] = {'categories': all_date_list[0]}
        xchar['yAxis'] = {'title': {'text': '因子累计收益率 (%)'}, 'plotLines': [{'value': 0, 'width': 1, 'color': '#808080'}]}
        xchar['tooltip'] = {'valueSuffix': '%'}
        xchar['legend'] = {'layout': 'vertical', 'align': 'right', 'verticalAlign': 'middle', 'borderWidth': 0}
        xchar['series'] = xy_xchar
        return response.json(
            {'status': 'ok', 'msg': 'msg', 'table': table, 'tchar': f_pre_chart,'tableFactorsGrids': tableFactorsGrids,'xchar':xchar})
    except Exception, e:
        print traceback.format_exc()
        if alpha_constant.multi_single_flag == 0: # 0:并行计算;1:单进程计算
            process_pool.terminate()
        return response.json({'status': 'error', 'msg': str(e)})

def create_alpha_limit_grid():
    try:
        policy_id = request.vars['policy_id']
        alpha_limit = json.loads(request.vars['alpha_limit'])
        df_obj = pd.DataFrame(data=alpha_limit, columns=['factor'])
        df_obj['upper_limit'] = 10.0
        df_obj['lower_limit'] = -10.0

        alpha_limit_list = []
        for index, row in df_obj.iterrows():
            alpha_limit_list.append(row.to_dict())

        sql = "DELETE FROM alpha_policy_json where type = " + alpha_constant.alpha_limit_list_type + " and policy_id = '" + policy_id + "'"  # 删除已有的策略轮动权重履历
        db.executesql(sql)
        sql = "INSERT INTO alpha_policy_json(type,policy_id,json)  VALUES(" + alpha_constant.alpha_limit_list_type + ",'" + policy_id + "','" + json.dumps(alpha_limit_list) + "')"
        db.executesql(sql)
        return response.json({'status': 'ok'})
    except Exception, e:
        print traceback.format_exc()
        return response.json({'status': 'error', 'msg': str(e)})

def update_alpha_limit_grid():
    try:
        policy_id = request.vars['policy_id']
        alpah_limit_grid = json.loads(request.vars['alpah_limit_grid'])
        sql = "update alpha_policy_json set json = '" + json.dumps(alpah_limit_grid) + "' where type = " + alpha_constant.alpha_limit_list_type + " and policy_id = '" + policy_id + "'"
        db.executesql(sql)
    except Exception, e:
        print traceback.format_exc()
        return response.json({'status': 'error', 'msg': str(e)})

def get_alpha_limit_grid():
    try:
        sql = "select json from alpha_policy_json where type = " + alpha_constant.alpha_limit_list_type + " and policy_id=" + request.vars['policy_id']
        limit_grid = db.executesql(sql)
        alpah_limit_grid = json.loads(limit_grid[0][0])
        return response.json({'status': 'ok','total':len(alpah_limit_grid),'rows':alpah_limit_grid})
    except Exception, e:
        print traceback.format_exc()
        return response.json({'status': 'error', 'msg': str(e)})

def get_date_range(start_date,end_date,freq_num=1):
    freq = DateOffset(months=freq_num)

    time_obj = pd.date_range(start=start_date, end=end_date, freq=freq)
    df_obj = pd.DataFrame(data=time_obj, columns=['start'])
    df_obj['end'] = df_obj['start'] + DateOffset(months=12)
    df_obj = df_obj[df_obj['end'] <= end_date]
    df_obj['end'][df_obj.shape[0] - 1] = pd.to_datetime(end_date)
    if df_obj.size != 0:
        df_obj['test_start'] = df_obj['end'] + DateOffset(days=1)
        df_obj['test_end'] = df_obj['test_start'] + DateOffset(months=1) - DateOffset(days=1)
    else:
        start = pd.to_datetime(start_date)
        end = pd.to_datetime(end_date)
        test_start = end + DateOffset(days=1)
        test_end = test_start + DateOffset(months=1)

        df_obj['start'] = [start]
        df_obj['end'] = end
        df_obj['test_start'] = test_start
        df_obj['test_end'] = test_end
    return df_obj

def construct_comb():
    """
    构建投资组合
    """
    try:
        fdata = json.loads(request.vars['fdata'])
        begin_date = request.post_vars['begindate']
        end_date = request.post_vars['enddate']
        policy_name = request.post_vars['policy_name']  # 策略名称
        policy_id = request.post_vars['policy_id']  # 策略名称
        opt_type = float(request.post_vars['majorizfunc'])  # 优化算法类型

        half_life_dict = get_half_life(policy_name)     # 获得 策略的 半衰参数

        f_alpha = []    # alpha 因子
        f_beta = []     # bata 因子
        for i, namef in enumerate(fdata):
            if fdata[namef] == "1":
                f_alpha.append(namef)
            if fdata[namef] == "2":
                f_beta.append(namef)

        df_date_range = get_date_range(begin_date, end_date)   # 滚动日期集合
        if df_date_range.size == 0:
            return response.json({'status': 'error'})

        sql = "DELETE FROM alpha_construct_comb where policy_name = '" + policy_name + "'"  # 删除已有的策略轮动权重履历
        db.executesql(sql)

        if alpha_constant.multi_single_flag == 1: # 0:并行计算;1:单进程计算
            process_pool = multiprocessing.Pool(alpha_constant.POOL_SIZE)  # 定义进程池数量

            parm_list = []
            for i, date_range in enumerate(df_date_range.values):
                parm_list.append((db, policy_id, policy_name, f_alpha, f_beta, opt_type,date_range, half_life_dict, i))

            results = process_pool.map(calc_optimize.multi_portfolio_construction, parm_list)
            process_pool.close()
            process_pool.join()

            for result in results:
                db.executesql(result)
        else:  # 单进程执行
            results = []
            for i, date_range in enumerate(df_date_range.values):
                results.append(calc_optimize.portfolio_construction(db, policy_id, policy_name, f_alpha, f_beta, opt_type,date_range, half_life_dict, i))

            for result in results:
                db.executesql(result)

        update_alpha_policy('step6',begindate=begin_date,enddate=end_date,policy_name=policy_name,step6_json='1',alpha_factors=json.dumps(f_alpha),bata_factors=json.dumps(f_beta))
        return response.json({'status': 'ok'})
    except Exception,e:
        print traceback.format_exc()
        if alpha_constant.multi_single_flag == 0: # 0:并行计算;1:单进程计算
            process_pool.terminate()
        return response.json({'status': 'error','msg': str(e)})

def get_half_life(policy_name):
    """
    获得 策略的 半衰参数
    :param policy_name: 策略名
    :return: half_life_dict
    """
    sql = "select predict_json from alpha_policy where policy_name = '" + policy_name + "'"
    predict_json = db.executesql(sql)
    half_life_dict = {}
    if len(predict_json) != 0:
        half_life_dict['var_half_life'] = 90
        half_life_dict['cov_half_life'] = 480
        half_life_dict['err_half_life'] = 42
    else:
        half_life_dict = json.loads(predict_json[0][0])
    return half_life_dict

def get_industry_dummies(trade_code_list,policy_id=None):
    """
    行业因子哑变量 DataFrame
    :param trade_code_list:股票集合
    :return: industry
    """
    industry = pd.DataFrame(data=get_stock_industry(), columns=['industry', 'tradecode'])
    industry.set_index('tradecode', inplace=True)
    if policy_id != None:  # 有效的行业因子code_list
        policy_dict = get_policy(policy_id=policy_id)
        industry_factors = policy_dict['industry_factors']
        industry = industry[industry.isin(industry_factors)]
    industry = industry.loc[trade_code_list]
    return pd.get_dummies(industry['industry'])  # 哑变量

def get_base_data(start_date,end_date):
    """
    根据日期获取策略基本数据
    :param start_date: 起始时间
    :param end_date: 结束时间
    :return: df_obj
    """
    stockdata = db.executesql("select * from alpha_stock_data where tradedate >= '" + start_date + "' and tradedate <= '" + end_date + "'")
    df_obj = pd.DataFrame(data=stockdata, columns=alpha_constant.stock_data_column_list)
    return df_obj

def get_hs300_weight(trade_date):
    """
    沪深300 权重
    :param trade_code_list: 股票list
    :return: 沪深300 权重
    """
    stockweight = get_alpha_hs300_weight(trade_date)
    hs300_weight = pd.DataFrame(data=stockweight, columns=['tradecode', 'trade_weight'])
    hs300_weight.set_index('tradecode', inplace=True)
    # hs300_weight = stockweightpd[stockweightpd['tradecode'].isin(trade_code_list)]['trade_weight'].values
    # return hs300_weight / hs300_weight.sum()
    return hs300_weight

def get_return_estimator(f,var_half_life=90,cov_half_life=480,err_half_life=42):
    """
    共同因子的风险预测
    :param f:风险模型
    :param var_half_life:方差半衰期
    :param cov_half_life:协方差半衰期
    :param err_half_life:误差半衰期
    :return: 共同因子的风险预测
    """
    Fd = F_d(f, var_half_life, cov_half_life)
    f = np.array(f)
    # btf01 = get_btf_test(f)
    btf = get_btf(f)
    rf = np.average(btf, weights=weight_btf(len(btf), err_half_life)) ** 0.5
    Fd1 = rf ** 2 * Fd
    return 21 * Fd1
    # return calc_newey_west(pd.DataFrame(f)) * Fd1

def get_risk_estimator(resid_df):
    """
    特质因子的风险预测
    :param resid_df: 残差
    :return:特质因子的风险预测
    """
    tradecode = resid_df.columns.tolist()
    std =[]
    for i in range(resid_df.shape[1]):
        data = resid_df.iloc[:,i]
        data.dropna(inplace = True)
        if data.shape[0]<=1:#可以添加一些阈值判断条件
            # std.append(np.nan)
            tradecode.remove(data.name)
        else:
            weight = weight_var(data.shape[0])
            weight_normalizaiton = weight/weight.sum()
            data_dev = (data - data.mean())**2
            data_std = (data_dev * weight_normalizaiton).sum()
            std.append(data_std)
    Ud = pd.DataFrame(np.diagflat(std),columns=tradecode,index=tradecode)
    return 21 * Ud

def str2timestemp(date_str):
    format_type = "%Y-%m-%d"
    timeArray = time.strptime(date_str, format_type)
    return int(time.mktime(timeArray)) * 1000

def retest():
    """
    执行回测
    """
    try:
        policy_id = request.post_vars['policy_id']
        sql = "SELECT retest_start_date,retest_end_date,comb_weights FROM alpha_construct_comb WHERE policy_name = (select policy_name from alpha_policy where id =" + policy_id + ") order by  seq"
        retest_obj = db.executesql(sql)
        retest_df = pd.DataFrame(data=retest_obj, columns=['retest_start_date', 'retest_end_date', 'comb_weights'])

        retest_dict = calc_retest02(retest_df)
        policy_returns_acc_list = retest_dict['policy_returns_acc']
        benchmark_returns_acc = retest_dict['benchmark_returns_acc']

        all_date_list = retest_dict['all_date_list']

        date_ser = all_date_list.apply(str2timestemp)
        policy_np = np.array([policy_returns_acc_list.values,benchmark_returns_acc.values]).T
        policy_df = pd.DataFrame(policy_np,columns=['policy_returns_acc','benchmark_returns_acc'])
        policy_df['date_list'] = date_ser.values

        testchar={}
        testchar['title']={'text': '累计收益率'}
        testchar['rangeSelector']={'buttons': [{'type': 'month','count': 1,'text': '一个月',},{'type': 'year','count': 1,'text': '一年',},{'type': 'all','text': '全部',}],'inputDateFormat':'%Y-%m-%d','inputEditDateFormat': '%Y-%m-%d','selected': 1}
        testchar['xAxis']={'type': 'datetime','dateTimeLabelFormats':{'day': '%Y-%m-%d','month': '%Y-%m','year': '%Y'}}
        testchar['yAxis']={'title': {'text': '因子累计收益率 (%)'},'plotLines': [{ 'value': 0, 'width': 1, 'color': '#808080'}]}
        testchar['tooltip']= {'valueSuffix':'%'}
        testchar['legend']={'layout': 'vertical','align': 'right','verticalAlign': 'middle','borderWidth': 0}
        testchar['series']=[{'name':'策略','data':policy_df[['date_list','policy_returns_acc']].values.tolist()},{'name':'基准','data':policy_df[['date_list','benchmark_returns_acc']].values.tolist()}]

        testrow = ""
        list_parm=['annualized_year','benchmark_year','alpha','bata','algor_volat','ir','max_drawdown','sharpe']
        for parm in list_parm:
            testrow = testrow+'<small><label class ="col-sm-1 control-label "> '+str(round(retest_dict[parm],2))+' </label> </small>'
        print testrow
        return response.json({'status': 'ok','testchar':testchar,'testrow':testrow})
    except Exception, e:
        print traceback.format_exc()
        return response.json({'status': 'error', 'msg': str(e)})

def calc_retest02(retest_df,factor_index=None, annual_trading_day=252.0):
    """
    回测计算
    :param retest_df: 回测开始日期，结束日期，组合权重 的 df
    :param factor_index: 因子索引
    :param annual_trading_day: 年交易日
    :return: retest_dict
    """
    retest_dict = {}
    start_date = retest_df['retest_start_date'][0]
    end_date = retest_df['retest_end_date'].values[-1]

    df_obj = get_base_data(start_date, end_date)
    all_date_list = df_obj['tradedate'].drop_duplicates().sort_values()

    retest_dict['all_date_list'] = all_date_list

    benchmark_returns = calc_benchmark_returns(all_date_list)  # 基准每日收益
    benchmark_returns_acc = (benchmark_returns + 1).cumprod() - 1  # 总基准每日累计收益

    all_policy_returns_list = []  # 总策略每日收益
    for index, row in retest_df.iterrows():
        if factor_index != None:
            sub_obj = pd.DataFrame(data=np.array(json.loads(row['model_weights'])[factor_index]).T,columns=['weight_w'])
            sub_obj['tradecode'] = pd.Series(json.loads(row['w_tradecode']))
            sub_policy_returns = calc_policy_returns(sub_obj, row['retest_start_date'],row['retest_end_date'])  # 策略每日收益
            all_policy_returns_list.extend(sub_policy_returns)
        else:
            sub_policy_returns = calc_policy_returns(pd.DataFrame(json.loads(row['comb_weights']), columns=['tradecode', 'weight_w']), row['retest_start_date'],row['retest_end_date'])  # 策略每日收益
            all_policy_returns_list.extend(sub_policy_returns)

    policy_returns = pd.Series(all_policy_returns_list)
    policy_returns_acc = (pd.Series(all_policy_returns_list) + 1).cumprod() - 1  # 策略每日累计收益

    retest_dict['policy_returns_acc'] = policy_returns_acc
    retest_dict['benchmark_returns_acc'] = benchmark_returns_acc

    benchmark_year = calc_annualized_year(benchmark_returns_acc, annual_trading_day)  # 基准年化收益
    retest_dict['benchmark_year'] = benchmark_year * 100.0

    annualized_year = calc_annualized_year(policy_returns_acc,annual_trading_day)  # 策略年化收益
    retest_dict['annualized_year'] = annualized_year * 100.0

    algor_volat = calc_algor_volat(policy_returns, annual_trading_day)  # 策略波动率
    retest_dict['algor_volat'] = algor_volat

    ir = calc_information_ratio(policy_returns.values, benchmark_returns.values, annual_trading_day)  # Information Ratio（信息比率）
    retest_dict['ir'] = ir

    max_drawdown = calc_max_drawdown(policy_returns_acc)  # 最大回撤
    retest_dict['max_drawdown'] = abs(max_drawdown) * 100.0

    bata = calc_bata(policy_returns, benchmark_returns)  # Beta 因子
    retest_dict['bata'] = bata

    alpha = calc_alpha(bata, annualized_year, benchmark_year)  # Alpha（阿尔法）
    retest_dict['alpha'] = alpha

    sharpe = calc_sharpe(annualized_year, algor_volat)  # 夏普比率
    retest_dict['sharpe'] = sharpe

    return retest_dict

def calc_retest(flag,all_benchmark_returns_acc=None,all_policy_returns_acc=None,start_date=None, end_date=None,comb_weights=None,df_obj=None, annual_trading_day=252.0):
    """
    计算回测指标
    :param flag: 0:组合风险预测; 1:策略回测
    :param all_benchmark_returns_acc: 总基准每日累计收益
    :param all_policy_returns_acc: 总策略每日累计收益
    :param comb_weights: 股票权重(type=0时才有值)
    :param start_date: 开始时间(type=0时才有值)
    :param end_date: 结束时间(type=0时才有值)
    :param annual_trading_day: 年交易日
    :return:retest_dict 指标 数据字典
    :return:
    """
    # 策略基础数据
    if df_obj is None:
        df_obj = get_base_data(start_date, end_date)
    date_list = df_obj['tradedate'].drop_duplicates().sort_values()

    policy_returns = calc_policy_returns(comb_weights, start_date, end_date)  # 策略每日收益

    benchmark_returns = calc_benchmark_returns(date_list)  # 基准每日收益
    if flag == 0:
        benchmark_returns_acc = (benchmark_returns + 1).cumprod() - 1  # 基准每日累计收益
        session.retest_dict['benchmark_returns_acc'].append(benchmark_returns_acc.tolist())

        policy_returns_acc = (policy_returns + 1).cumprod() - 1  # 策略每日累计收益
        session.retest_dict['policy_returns_acc'].append(policy_returns_acc.tolist())

    elif flag == 1:
        benchmark_returns_acc = all_benchmark_returns_acc[date_list]
        policy_returns_acc = all_policy_returns_acc

    benchmark_year = calc_annualized_year(benchmark_returns_acc, annual_trading_day)  # 基准年化收益
    session.retest_dict['benchmark_year'].append(benchmark_year * 100.0)

    annualized_year = calc_annualized_year(policy_returns_acc,annual_trading_day)  # 策略年化收益
    session.retest_dict['annualized_year'].append(annualized_year * 100.0)

    algor_volat = calc_algor_volat(policy_returns,annual_trading_day)   # 策略波动率
    session.retest_dict['algor_volat'].append(algor_volat)

    downside_risk = calc_downside_risk(benchmark_returns,annual_trading_day)   # 下行波动率

    ir = calc_information_ratio(policy_returns,benchmark_returns, annual_trading_day)    # Information Ratio（信息比率）
    session.retest_dict['ir'].append(ir)

    max_drawdown = calc_max_drawdown(policy_returns_acc)    # 最大回撤
    session.retest_dict['max_drawdown'].append(abs(max_drawdown) * 100.0)

    if flag == 1:   # 回测时计算的 额外指标
        session.retest_dict['date_list'] = date_list

        bata = calc_bata(policy_returns, benchmark_returns)  # Beta 因子
        session.retest_dict['bata'].append(bata)

        alpha = calc_alpha(bata, annualized_year, benchmark_year)  # Alpha（阿尔法）
        session.retest_dict['alpha'].append(alpha)

        sharpe = calc_sharpe(annualized_year, algor_volat)  # 夏普比率
        session.retest_dict['sharpe'].append(sharpe)

    if flag == '还没使用指标':
        calc_benchmark_bolat(benchmark_returns, annual_trading_day)  # 基准波动率
        calc_sortino(annualized_year, downside_risk)    # Sortino（索提诺比率）
        calc_day_win(policy_returns, benchmark_returns) #日胜率

################ 回测指标计算方法 start #################

def calc_benchmark_returns(date_list):
    """
    计算基准每日收益
    :param date_list: 日期列表
    :return:
    """
    df_hs300_x = pd.DataFrame(data=get_hs300_data(), columns=alpha_constant.hs300_data_column_list)
    df_hs300_ser = pd.Series(data=df_hs300_x['rets'].values,index=df_hs300_x['tradedate'])
    return df_hs300_ser[date_list]

def calc_policy_returns(comb_weights,start_date,end_date):
    """
    计算策略每日收益
    :param comb_weights: 股票权重
    :param start_date: 开始时间
    :param end_date: 结束时间
    :return: 计算策略每日的累计收益
    """
    df_obj = get_base_data(start_date, end_date)  # 策略基础数据
    date_list = df_obj['tradedate'].drop_duplicates().sort_values()
    df_day_code_colse = df_obj[df_obj['tradedate'].isin(date_list)].loc[:, ['tradecode', 'tradedate', 'rets']]
    df_day_code_colse_new = pd.merge(comb_weights, df_day_code_colse, how='right')
    df_day_code_colse_new['weight_close'] = df_day_code_colse_new['rets'] * df_day_code_colse_new['weight_w']
    df_day_code_colse_new_su = df_day_code_colse_new.groupby('tradedate').weight_close.sum()
    benchmark_returns = calc_benchmark_returns(date_list)  # 基准每日收益
    policy_returns_tatol = pd.Series(data=df_day_code_colse_new_su.values - benchmark_returns.values,index=df_day_code_colse_new_su.index)  # 计算策略每日收益
    return policy_returns_tatol

def calc_policy_returns01(flag,retest_df=None,comb_weights=None,start_date=None,end_date=None):
    """
    计算策略每日收益
    :param flag: 1:回测; 0:风险预测
    :param retest_df: 策略回测日期及股票权重 集合
    :param comb_weights: 股票权重(type=0时才有值)
    :param start_date: 开始时间(type=0时才有值)
    :param end_date: 结束时间(type=0时才有值)
    :return: 计算策略每日的累计收益
    """
    if flag == 1: #回测阶段
        for i, date_range in enumerate(retest_df.values):
            weight_w = pd.DataFrame(data=json.loads(date_range[2]), columns=['tradecode', 'weight_w'])
            df_obj = get_base_data(date_range[0], date_range[1])  # 策略基础数据
            date_list = df_obj['tradedate'].drop_duplicates().sort_values()
            df_day_code_colse = df_obj[df_obj['tradedate'].isin(date_list)].loc[:, ['tradecode', 'tradedate', 'rets']]
            df_day_code_colse_new = pd.merge(weight_w, df_day_code_colse, how='right')
            df_day_code_colse_new['weight_close'] = df_day_code_colse_new['rets'] * df_day_code_colse_new['weight_w']
            df_day_code_colse_new_su = df_day_code_colse_new.groupby('tradedate').weight_close.sum()
            benchmark_returns = calc_benchmark_returns(date_list)  # 基准每日收益
            policy_returns = pd.Series(data=df_day_code_colse_new_su.values - benchmark_returns.values,index=df_day_code_colse_new_su.index)  # 计算策略每日收益
            if i == 0:
                policy_returns_tatol = policy_returns
            else:
                policy_returns_tatol = policy_returns_tatol.append(policy_returns)
    elif flag == 0: #风险预测阶段
        df_obj = get_base_data(start_date, end_date)  # 策略基础数据
        date_list = df_obj['tradedate'].drop_duplicates().sort_values()
        df_day_code_colse = df_obj[df_obj['tradedate'].isin(date_list)].loc[:, ['tradecode', 'tradedate', 'rets']]
        df_day_code_colse_new = pd.merge(comb_weights, df_day_code_colse, how='right')
        df_day_code_colse_new['weight_close'] = df_day_code_colse_new['rets'] * df_day_code_colse_new['weight_w']
        df_day_code_colse_new_su = df_day_code_colse_new.groupby('tradedate').weight_close.sum()
        benchmark_returns = calc_benchmark_returns(date_list)  # 基准每日收益
        policy_returns_tatol = pd.Series(data=df_day_code_colse_new_su.values - benchmark_returns.values,index=df_day_code_colse_new_su.index)  # 计算策略每日收益
    return policy_returns_tatol

def calc_annualized_year(policy_returns_acc,annual_trading_day=252.0):
    """
    年化收益公式
    :param policy_returns_acc: 每日累计收益
    :param annual_trading_day: 年交易日
    :return: 策略年化收益
    """
    return (1 + policy_returns_acc[policy_returns_acc.shape[0]-1]) ** (annual_trading_day/policy_returns_acc.shape[0]) - 1 # 假设数据开始当日开盘时即完成交易，并假设没有手续费

def calc_bata(policy_returns,benchmark_returns):
    """
    计算 Beta 因子
    :param policy_returns: 策略每日收益
    :param benchmark_returns: 基准每日收益
    :return: Beta 因子
    """
    cov_array = np.cov(policy_returns, benchmark_returns)
    return cov_array[0, 1] / np.var(benchmark_returns)

def calc_alpha(bata, annualized_year, benchmark_year, r_f=0.04):
    """
    计算 Alpha 因子
    :param bata: Beta 因子
    :param annualized_year: 策略年化收益
    :param benchmark_year: 基准年化收益
    :param r_f: 无风险利率
    :return: Alpha 因子
    """
    return annualized_year - (r_f + bata * (benchmark_year - r_f))

def calc_algor_volat(policy_returns,annual_trading_day=252.0):
    """
    计算 策略波动率
    :param policy_returns:  策略每日收益
    :param annual_trading_day: 年交易日
    :return: 策略波动率
    """
    return np.std(policy_returns.values) * (annual_trading_day ** .5)

def calc_benchmark_bolat(benchmark_returns,annual_trading_day=252.0):
    """
    计算 基准波动率
    :param benchmark_returns: 基准每日收益
    :param annual_trading_day: 年交易日
    :return: 基准波动率
    """
    return np.std(benchmark_returns.values) * (annual_trading_day ** .5)

def calc_sharpe(annualized_year, algor_volat, r_f=0.04):
    """
    计算 Sharpe 比率
    :param annualized_year: 策略年化收益
    :param algor_volat: 策略波动率
    :param r_f: 无风险利率
    :return: Sharpe 比率
    """
    return (annualized_year - r_f) / algor_volat

def calc_downside_risk(benchmark_returns,annual_trading_day=252.0):
    """
    计算 下行波动率
    :param benchmark_returns: 基准每日收益
    :param annual_trading_day: 年交易日
    :return: 下行波动率
    """
    mu = np.mean(benchmark_returns.values)
    temp = benchmark_returns[benchmark_returns<mu]
    return (sum((mu-temp)**2)/(annual_trading_day / benchmark_returns.size)) ** 0.5

def calc_sortino(annualized_year, downside_risk, r_f=0.04):
    """
    计算 Sortino（索提诺比率）
    :param annualized_year: 策略年化收益
    :param downside_risk: 下行波动率
    :param r_f: 无风险利率
    :return: Sortino（索提诺比率）
    """
    return (annualized_year - r_f) / downside_risk

def calc_information_ratio(policy_returns,benchmark_returns, annual_trading_day=252.0):
    """
    计算 Information Ratio（信息比率）
    :param policy_returns: 策略每日收益
    :param benchmark_returns: 基准每日收益
    :param annual_trading_day: 年交易日
    :return: 信息比率
    """
    ir_df = np.mean(policy_returns-benchmark_returns) * annual_trading_day
    Q_t = np.std(policy_returns - benchmark_returns) * (annual_trading_day ** 0.5)
    return ir_df / Q_t

def calc_max_drawdown(policy_returns_acc):
    """
    计算 Max Drawdown（最大回撤）
    :param policy_returns_acc: 策略每日累计收益
    :return: 最大回撤
    """
    policy_returns_acc = policy_returns_acc + 1
    return (policy_returns_acc / policy_returns_acc.expanding(min_periods=1).max()).min() - 1

def calc_day_win(policy_returns, benchmark_returns):
    """
    计算 日胜率(%)
    :param policy_returns: 策略每日收益
    :param benchmark_returns: 基准每日收益
    :return: 日胜率(%)
    """
    return policy_returns[policy_returns-benchmark_returns > 0].shape[0] /  policy_returns.shape[0]

################ 回测指标计算方法 end #################

def risk_attrib():
    '''
    风险归因
    :return:
    '''
    try:
        policy_id = request.post_vars['policy_id']
        start_date = request.post_vars['begindate']
        end_date = request.post_vars['enddate']

        policy_dict = get_policy(policy_id=policy_id)
        if float(policy_dict['comb_weights']) != 1:
            return response.json({'status': 'error', 'msg': '请先完善策略流程！'})
        # session.policy_model_dict[policy_id] = policy_dict  # 策略缓存

        df_obj = get_base_data(start_date,end_date)     # 策略基础数据
        code_list = df_obj['tradecode'].drop_duplicates()

        hs300_weight = get_hs300_weight(policy_dict['end_date'])      # 沪深300 权重

        sql = "SELECT comb_weights FROM alpha_construct_comb where policy_name = '"+policy_dict['policy_name']+"' and seq = (SELECT max(seq) FROM alpha_construct_comb where policy_name = '"+policy_dict['policy_name']+"')"
        min_weight = pd.DataFrame(data=json.loads(db.executesql(sql)[0][0]),columns=['tradecode', 'weight_w']) #组合权重 alpha_construct_comb 最后一行
        min_weight.set_index('tradecode',inplace=True)
        x_list = policy_dict['style_factors']    # alpha_policy style_factors 列
        new_x_list = policy_dict['all_factors']      # alpha_policy style_factors 'extend' industry_factors 和
        industry_columns = policy_dict['industry_factors'] # alpha_policy industry_factors

        model_dict = alpha_common.build_model(db,policy_id,start_date, end_date,df_obj)  # 模型构建
        f_attribute = model_dict['f']

        industry = get_industry_dummies(code_list,policy_id)  # 行业因子哑变量 DataFrame
        industry.reset_index(inplace=True)
        df_obj = pd.merge(df_obj, industry, on='tradecode')  # 加上行业因子的df

        factor_attribute_returns = pd.DataFrame(f_attribute, columns=new_x_list)  # 回测区间内，共同因子每日收益

        factor_attribute_exposure_columns = new_x_list + ['tradecode']
        factor_attribute_exposure = df_obj[factor_attribute_exposure_columns]  # 回测区间内因子暴露
        factor_attribute_exposure_mean = factor_attribute_exposure.groupby("tradecode").mean()  # 回测区间内因子暴露的均值
        factor_F = np.cov(factor_attribute_returns.T)  # 回测区间内的因子风险矩阵
        factor_attribute_risk = (np.dot(np.dot(min_weight.T - hs300_weight, factor_attribute_exposure_mean), factor_F) * \
                                np.dot(min_weight.T - hs300_weight, factor_attribute_exposure_mean)) * 100.0  # 回测时间内，投资组合的因子获取的收益归因

        factor_attribute_risk_df = pd.DataFrame(data=new_x_list,columns=['f_name'])

        factor_attribute_risk_df['f_pre'] = factor_attribute_risk.T
        # factor_attribute_risk_df['f_pre'] = factor_attribute_risk_df['f_pre'].astype('float64')
        ###################### 风格因子绩效归因 ##########################
        f_pre_df = factor_attribute_risk_df[factor_attribute_risk_df['f_name'].isin(x_list)].copy()
        f_pre_df.sort_values('f_pre', inplace=True, ascending=False)
        f_pre_series = []
        f_pre_series_dict = {}
        f_pre_series_dict['name'] = '风格因子'
        f_pre_series_dict['data'] = f_pre_df['f_pre'].values.tolist()
        f_pre_series.append(f_pre_series_dict)

        f_pre_chart = {}
        f_pre_chart['chart'] = {'type': 'column'}
        f_pre_chart['title'] = {'text': '风格因子风险归因'}
        f_pre_chart['subtitle'] = {'text': ''}
        f_pre_chart['xAxis'] = {'categories': f_pre_df['f_name'].values.tolist()}
        f_pre_chart['yAxis'] = {'title': {'text': '风格因子收益归因 (%)'},'plotLines': [{'value': 0, 'width': 1, 'color': '#808080'}]}
        f_pre_chart['tooltip'] = {'valueSuffix': '%'}
        f_pre_chart['legend'] = {'layout': 'vertical', 'align': 'right', 'verticalAlign': 'middle', 'borderWidth': 0}
        f_pre_chart['series'] = f_pre_series

        ###################### 行业因子绩效归因 ##########################
        f_inds_df = factor_attribute_risk_df[factor_attribute_risk_df['f_name'].isin(industry_columns)].copy()
        f_inds_df.sort_values('f_pre', inplace=True, ascending=False)
        f_inds_series = []
        f_inds_series_dict = {}
        f_inds_series_dict['name'] = '行业因子'
        f_inds_series_dict['data'] = f_inds_df['f_pre'].values.tolist()
        f_inds_series.append(f_inds_series_dict)

        f_inds_chart = {}
        f_inds_chart['chart'] = {'type': 'column'}
        f_inds_chart['title'] = {'text': '行业因子风险归因'}
        f_inds_chart['subtitle'] = {'text': ''}
        f_inds_chart['xAxis'] = {'categories': f_inds_df['f_name'].values.tolist()}
        f_inds_chart['yAxis'] = {'title': {'text': '行业因子收益归因 (%)'}, 'plotLines': [{'value': 0, 'width': 1, 'color': '#808080'}]}
        f_inds_chart['tooltip'] = {'valueSuffix': '%'}
        f_inds_chart['legend'] = {'layout': 'vertical', 'align': 'right', 'verticalAlign': 'middle', 'borderWidth': 0}
        f_inds_chart['series'] = f_inds_series

        return response.json({'status': 'ok', 'f_pre_chart': f_pre_chart,'f_inds_chart': f_inds_chart})
    except Exception, e:
        print traceback.format_exc()
        return response.json({'status': 'error', 'msg': str(e)})

def perf_attrib():
    '''
    绩效归因
    :return:
    '''
    try:
        policy_id = request.post_vars['policy_id']
        start_date = request.post_vars['begindate']
        end_date = request.post_vars['enddate']

        policy_dict = get_policy(policy_id=policy_id)
        if float(policy_dict['comb_weights']) != 1:
            return response.json({'status': 'error', 'msg': '请先完善策略流程！'})

        df_obj = get_base_data(start_date, end_date)  # 策略基础数据
        code_list = df_obj['tradecode'].drop_duplicates()
        date_list = df_obj['tradedate'].drop_duplicates().sort_values()

        hs300_weight = get_hs300_weight(date_list[0])  # 沪深300 权重

        sql = "SELECT comb_weights FROM alpha_construct_comb where policy_name = '" + policy_dict['policy_name'] + "' and seq = (SELECT max(seq) FROM alpha_construct_comb where policy_name = '" + policy_dict['policy_name'] + "')"
        min_weight = pd.DataFrame(data=json.loads(db.executesql(sql)[0][0]),columns=['tradecode', 'weight_w'])  # 组合权重 alpha_construct_comb 最后一行
        min_weight.set_index('tradecode', inplace=True)

        x_list = policy_dict['style_factors']  # alpha_policy style_factors 列
        new_x_list = policy_dict['all_factors']  # alpha_policy style_factors 'extend' industry_factors 和
        industry_columns = policy_dict['industry_factors']  # alpha_policy industry_factors

        model_dict = alpha_common.build_model(db,policy_id,start_date, end_date, df_obj)  # 模型构建
        factor_attribute = pd.DataFrame(data=model_dict['f'],columns=new_x_list)
        factor_attribute_acc = ((factor_attribute+1).cumprod())[-1:]-1
        # pd.Series(model_dict['resid_df'].columns.values)
        industry = get_industry_dummies(code_list,policy_id)  # 行业因子哑变量 DataFrame
        industry.reset_index(inplace=True)
        df_obj = pd.merge(df_obj, industry, on='tradecode')  # 加上行业因子的 df

        factor_attribute_exposure_columns = new_x_list + ['tradecode']
        factor_attribute_exposure = df_obj[factor_attribute_exposure_columns]  # 回测区间内因子暴露
        factor_attribute_exposure_mean = factor_attribute_exposure.groupby("tradecode").mean()#回测区间内因子暴露的均值
        attribute_returns_mean = pd.DataFrame(np.dot(min_weight.T - hs300_weight, factor_attribute_exposure_mean) * factor_attribute_acc, columns=new_x_list)

        ###################### 风格因子绩效归因 ##########################
        f_pre_attrib = attribute_returns_mean[x_list]
        f_pre_df = pd.DataFrame(data=f_pre_attrib.T.reset_index().values, columns=['f_name', 'f_pre'])
        f_pre_df.sort_values('f_pre', inplace=True, ascending=False)
        f_pre_series = []
        f_pre_series_dict = {}
        f_pre_series_dict['name'] = '风格因子'
        f_pre_series_dict['data'] = f_pre_df['f_pre'].values.tolist()
        f_pre_series.append(f_pre_series_dict)

        f_pre_chart = {}
        f_pre_chart['chart'] = {'type': 'column'}
        f_pre_chart['title'] = {'text': '风格因子收益归因'}
        f_pre_chart['subtitle'] = {'text': ''}
        f_pre_chart['xAxis'] = {'categories': f_pre_df['f_name'].values.tolist()}
        f_pre_chart['yAxis'] = {'title': {'text': '风格因子收益归因 (%)'},'plotLines': [{'value': 0, 'width': 1, 'color': '#808080'}]}
        f_pre_chart['tooltip'] = {'valueSuffix': '%'}
        f_pre_chart['legend'] = {'layout': 'vertical', 'align': 'right', 'verticalAlign': 'middle', 'borderWidth': 0}
        f_pre_chart['series'] = f_pre_series

        ###################### 行业因子绩效归因 ##########################
        f_inds_attrib = attribute_returns_mean[industry_columns]
        f_inds_df = pd.DataFrame(data=f_inds_attrib.T.reset_index().values, columns=['f_name', 'f_pre'])
        f_inds_df.sort_values('f_pre', inplace=True, ascending=False)
        f_inds_series = []
        f_inds_series_dict = {}
        f_inds_series_dict['name'] = '行业因子'
        f_inds_series_dict['data'] = f_inds_df['f_pre'].values.tolist()
        f_inds_series.append(f_inds_series_dict)

        f_inds_chart = {}
        f_inds_chart['chart'] = {'type': 'column'}
        f_inds_chart['title'] = {'text': '行业因子收益归因'}
        f_inds_chart['subtitle'] = {'text': ''}
        f_inds_chart['xAxis'] = {'categories': f_inds_df['f_name'].values.tolist()}
        f_inds_chart['yAxis'] = {'title': {'text': '行业因子收益归因 (%)'}, 'plotLines': [{'value': 0, 'width': 1, 'color': '#808080'}]}
        f_inds_chart['tooltip'] = {'valueSuffix': '%'}
        f_inds_chart['legend'] = {'layout': 'vertical', 'align': 'right', 'verticalAlign': 'middle', 'borderWidth': 0}
        f_inds_chart['series'] = f_inds_series

        return response.json({'status': 'ok', 'f_pre_chart': f_pre_chart,'f_inds_chart': f_inds_chart})
    except Exception, e:
        print traceback.format_exc()
        return response.json({'status': 'error', 'msg': str(e)})

def get_policy_list():
    try:
        # process_pool = multiprocessing.Pool(processes=alpha_constant.POOL_SIZE)
        # list = []
        # for i in range(5):
        #     list.append((db, 5,i))
        # results = process_pool.map(calc_optimize.mulit_func_02, list)
        # process_pool.close()
        # process_pool.join()
        #
        # for res in results:
        #     print db.executesql(res)

        #//////////////////////
        policy_list = db.executesql("select id,policy_name,create_date,comb_weights from alpha_policy")
        result_policy = []
        for item in policy_list:
            policy_dict = {}
            policy_dict['id'] = item[0]
            policy_dict['policy_name'] = item[1]
            policy_dict['create_date'] = item[2]
            policy_dict['comb_weights'] = item[3] == '1' and '完成' or '未完成'
            result_policy.append(policy_dict)
        # sortOrder = request.get_vars['sortOrder']
        # sortName = request.get_vars['sortName']
        # lstRes = order_table(sortName, sortOrder, result_policy)
        # return response.json({'status': 'ok','total':len(lstRes),'rows':lstRes})
        return response.json({'status': 'ok','total':len(result_policy),'rows':result_policy})
    except Exception, e:
        print traceback.format_exc()
        # if alpha_constant.multi_single_flag == 0: # 0:并行计算;1:单进程计算
        #     process_pool.terminate()
        return response.json({'status': 'error', 'msg': str(e)})

def get_policy(**arg_dict):
    if arg_dict.has_key('policy_id'):
        sql = "select id,policy_name,create_date,select_factor_json,predict_json,style_factors,industry_factors,alpha_factors,bata_factors,comb_weights,create_date,all_factors,end_date from alpha_policy where id=" + arg_dict['policy_id']
    elif arg_dict.has_key('policy_name'):
        sql = "select id,policy_name,create_date,select_factor_json,predict_json,style_factors,industry_factors,alpha_factors,bata_factors,comb_weights,create_date,all_factors,end_date from alpha_policy where policy_name = '"+arg_dict['policy_name']+"'"
    else:
        return None
    policy_list = db.executesql(sql)
    policy_dict={}
    policy_dict['id'] = policy_list[0][0]
    policy_dict['policy_name'] = policy_list[0][1]
    policy_dict['create_date'] = policy_list[0][2]
    policy_dict['select_factor_json'] = policy_list[0][3] is None and '[]' or json.loads(policy_list[0][3])
    policy_dict['predict_json'] = policy_list[0][4] is None and '[]' or json.loads(policy_list[0][4])
    policy_dict['style_factors'] = policy_list[0][5] is None and '[]' or json.loads(policy_list[0][5])
    policy_dict['industry_factors'] = policy_list[0][6] is None and '[]' or json.loads(policy_list[0][6])
    policy_dict['alpha_factors'] = policy_list[0][7] is None and '[]' or json.loads(policy_list[0][7])
    policy_dict['bata_factors'] = policy_list[0][8] is None and '[]' or json.loads(policy_list[0][8])
    policy_dict['comb_weights'] = policy_list[0][9] is None and '[]' or json.loads(policy_list[0][9])
    policy_dict['create_date'] = policy_list[0][10]
    policy_dict['all_factors'] = policy_list[0][11] is None and '[]' or json.loads(policy_list[0][11])
    policy_dict['end_date'] = policy_list[0][12]
    return policy_dict

def create_policy():
    """
    创建策略
    :return:
    """
    try:
        policy_name = request.post_vars['policy_name']
        sql = "select id from alpha_policy where policy_name = '" + policy_name + "'"
        flag = db.executesql(sql)
        if len(flag) != 0:
            return response.json({'status': 'error', 'msg': '此策略已存在.'})

        create_date = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        db.executesql("INSERT INTO alpha_policy(policy_name,create_date) VALUES('"+policy_name+"','"+create_date+"')")

        policy_id = db.executesql("select id from alpha_policy where policy_name = '" + policy_name + "'")
        return response.json({'status': 'ok', 'msg': 'msg', 'policy_id': str(policy_id[0][0])})
    except Exception, e:
        print traceback.format_exc()
        return response.json({'status': 'error', 'msg': str(e)})

def remove_policy():
    try:
        policy_id = request.post_vars['policy_id']
        policy_name = request.post_vars['policy_name']
        db.executesql("DELETE FROM alpha_policy WHERE id =" + policy_id)
        db.executesql("DELETE FROM alpha_construct_comb WHERE policy_name ='" + policy_name + "'")
        return response.json({'status': 'ok', 'msg': 'msg'})
    except Exception, e:
        print traceback.format_exc()
        return response.json({'status': 'error', 'msg': str(e)})